'''
Created on Jul 31, 2012

@author: peng
'''
import MySQLdb


class MySQLdbWrapper:

    conn = None

    def connect(self):
        self.conn = MySQLdb.connect(host='127.0.0.1',
                                    user='root',
                                    passwd='beluga',
                                    db='beluga_api',
                                    port=3306)
        self.conn.set_character_set('utf8')
        self.conn.autocommit(True)

    def cursor(self):
        try:
            # if not self.conn:
            self.connect()
            return self.conn.cursor()
        except MySQLdb.OperationalError:
            self.connect()
            return self.conn.cursor()

_db = MySQLdbWrapper()


def _get_data(sql_list):
    try:
        cursor = _db.cursor()
        sql = ' '.join(sql_list)
        cursor.execute(sql)
        data = cursor.fetchall()
        return data
    finally:
        cursor.close()

f = open('/home/ubuntu/merge/output/retention_monthly.out')
count = 0
for l in f.readlines():
    values = l.split('\t')
    sql_list = []
    sql_list.append("select count(*) from retention_monthly")
    sql_list.append("where channel='%s'" % values[2])
    sql_list.append("and version='%s'" % values[3])
    sql_list.append("and date='%s'" % values[4])
    result = _get_data(sql_list)
    if result[0][0] == 0:
        count += 1
        print l
print 'count [%s]' % count
